<?php
namespace app\controller;

use think\Request;
use think\facade\Db;
use app\model\Bazaar;
use app\model\Expand;
use app\model\Customer;
use app\model\Principal;

class SemSearch extends Common
{

	/**
	 * 请求类型
	 *
	 * @return
	 */
	public function read(Request $request)
	{
		if($this->hasPermission($request->sessInfo, 'semsearch:read')) {
			return ['code'=> 403, 'msg'=> '你没有权限执行此操作!'];;
		}

		$where = [];
		$limit = $request->param('limit/d', 20);
		$sort = $request->param('sort', 'id', 'trim');
		$order = $request->param('order', 'desc', 'trim');
		if( !in_array($sort, ['accessnumber']) ) {
			$sort = 's.'.$sort;
		}

		array_push( $where, ['s.companyId', '=', $request->sessInfo['companyId'] ] );
		array_push( $where, ['s.userType', '=', 1 ] );

		$bazaarId = $request->param('bazaarId', '', 'trim');
		if($bazaarId) {
			array_push( $where, ['s.bazaarId', '=',  $bazaarId ] );
		}

		$userid = $request->param('userid', '', 'trim');
		if($userid) {
			array_push( $where, ['s.userId', '=',  $userid ] );
		}
		
		$customerid = $request->param('customerid', '', 'trim');
		if($customerid) {
			array_push( $where, ['s.customerid', '=',  $customerid ] );
		}

		$contact = $request->param('contact', '', 'trim');
		if($contact) {
			array_push( $where, ['s.contact', '=',  $contact ] );
		}

		$createTimeStart = $request->param('createTimeStart', '', 'trim');
		$createTimeEnd = $request->param('createTimeEnd', '', 'trim');
		if( $createTimeStart && $createTimeEnd ) {
			$time = cloud_datetime_format( $createTimeStart, $createTimeEnd, false);
		} else {
			$time = cloud_datetime_format( date('Y-m-d 00:00:00'), date('Y-m-d 00:00:00', strtotime("+1 day")), false);
		}
		array_push( $where, ['s.datatime', 'between', [ strtotime($time[0]), strtotime($time[1])] ] );

		$search = [];
		array_push( $search, " a.companyId = {$request->sessInfo['companyId']} ");
		array_push( $search, " a.userType = 1 ");
		array_push( $search, " a.accessTime BETWEEN '{$time[0]}' AND '{$time[1]}' ");
		array_push( $search, " a.userid = s.userid ");
		array_push( $search, " a.word = s.word ");
		       $roleTempId = Db::table('cfg_user')->where('id', $request->sessInfo['id'])->value('role');
		if($roleTempId) {
			$authaccount = Db::table('cfg_role')->where('companyId', $request->sessInfo['companyId'])->where('id', $roleTempId)->value('semauthaccount');
			if($authaccount) {
				array_push( $where, ['s.userId', 'in',  $authaccount ] );
			}
		}
		$query = Db::table('cfg_search')
		->alias('s')
		->field('s.word,
		  count(distinct a.id) as accessnumber,
		  sum(s.impression) as impression,
			sum(s.clicks) as clicks,
			sum(s.cost) as cost,
			sum(s.cash) as cash,
			avg(s.rebate) as rebate,
			sum(s.copynumber) as copynumber,
			ROUND( sum(`cost`) / sum(`clicks`), 2) as `cpc`,
			ROUND( sum(`clicks`) / sum(`impression`) * 100, 2) as `ctr`,
			ROUND( sum(`cost`) / count(distinct a.id), 2 ) as `accesscost`,
			IF(ROUND( sum(`cost`) / sum(`copynumber`), 2 ), ROUND( sum(`cost`) / sum(`copynumber`), 2 ), sum(`cost`)) as `copycost`')
		->leftJoin('cfg_access a', implode(' AND ', $search))
		->where($where)
		->group('s.word')
		->order($sort, $order)
		->paginate($limit);

		$items = $query->items();
		foreach($items as &$value ) {
			$value['impression'] = $value['impression'] ? $value['impression'] : 0;
			$value['clicks'] = $value['clicks'] ? $value['clicks'] : 0;
			$value['cost'] = $value['cost'] ? $value['cost'] : 0;
			$value['cash'] = $value['cash'] ? $value['cash'] : 0;
			$value['rebate'] = $value['rebate'] ? $value['rebate'] : 0;
			$value['cpc'] = $value['cpc'] ? $value['cpc'] : 0;
			$value['ctr'] = $value['ctr'] ? $value['ctr'] : 0;
			$value['copynumber'] = $value['copynumber'] ? $value['copynumber'] : 0;
			$value['copycost'] = $value['copycost'] ? $value['copycost'] : 0;
			$value['accessnumber'] = $value['accessnumber'] ? $value['accessnumber'] : 0;
			$value['accesscost'] = $value['accesscost'] ? $value['accesscost'] : 0;
		}

		$total = Db::table('cfg_search')
			->alias('s')
			->field('count(distinct a.id) as accessnumber,
				sum(s.impression) as impression,
				sum(s.clicks) as clicks,
				sum(s.cost) as cost,
				sum(s.cash) as cash,
				avg(s.rebate) as rebate,
				sum(s.copynumber) as copynumber,
				ROUND( sum(`cost`) / sum(`clicks`), 2) as `cpc`,
				ROUND( sum(`clicks`) / sum(`impression`) * 100, 2) as `ctr`,
				ROUND( sum(`cost`) / count(distinct a.id), 2 ) as `accesscost`,
				IF(ROUND( sum(`cost`) / sum(`copynumber`), 2 ), ROUND( sum(`cost`) / sum(`copynumber`), 2 ), sum(`cost`)) as `copycost`')
		  ->leftJoin('cfg_access a', implode(' AND ', $search))
			->where($where)
			->find();

		$total['impression'] = $total['impression'] ? $total['impression'] : 0;
		$total['clicks'] = $total['clicks'] ? $total['clicks'] : 0;
		$total['cost'] = $total['cost'] ? $total['cost'] : 0;
		$total['cash'] = $total['cash'] ? $total['cash'] : 0;
		$total['rebate'] = $total['rebate'] ? $total['rebate'] : 0;
		$total['cpc'] = $total['cpc'] ? $total['cpc'] : 0;
		$total['ctr'] = $total['ctr'] ? $total['ctr'] : 0;
		$total['copynumber'] = $total['copynumber'] ? $total['copynumber'] : 0;
		$total['copycost'] = $total['copycost'] ? $total['copycost'] : 0;
		$total['accessnumber'] = $total['accessnumber'] ? $total['accessnumber'] : 0;
		$total['accesscost'] = $total['accesscost'] ? $total['accesscost'] : 0;

		return json(['code'=> 0, 'msg'=> '', 'count'=> $query->total(), 'data'=> $items, 'total' => $total,
			'expand' => Expand::getList($request->sessInfo, 1),
		  'customer' => Customer::getList($request->sessInfo),
		  'principal' => Principal::getList($request->sessInfo),
			'bazaar' => Bazaar::getList($request->sessInfo, 1)]);
	}

}
